/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package de.stalinski.db;

import de.stalinski.logging.FrbrLogger;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import javax.servlet.http.HttpServlet;

public class ConsumersConnector extends HttpServlet {

    private FrbrLogger logger = FrbrLogger.instance();
    private static String sUsr = "root";
    private static String sPwd = "admin";
    private static String sDB = "frbrUsers";
    private static String sTable = "customers";
    private static Connection connection;
    private static String sHost = "localhost";
    private static String sConn = "jdbc:mysql://" + sHost + ":3306/" + sDB;
    private ArrayList<String> customers = new ArrayList();
    private HashMap forename = new HashMap();
    private HashMap surname = new HashMap();

    public ConsumersConnector() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
        try {
            connection = DriverManager.getConnection(sConn, sUsr, sPwd);
            System.out.println(connection != null);
        } catch (SQLException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
    }

    public ArrayList<String> getSorts() throws SQLException {
        return new PricesTableConnector().getSorts();
    }

    public HashMap<String, Double> getPrices() throws SQLException {
        return new PricesTableConnector().getPricesAsHash();
    }

    public String showConsumption(String user) throws SQLException {
        String out = "<table class=\"Prices\">" + "<thead><th>Sorte</th><th>Verbrauch</th><th>Kosten</th></thead>" +
                "<tbody>" +
                "<tr>";
        Statement st = null;
        ResultSet rs = null;
        st = connection.createStatement();
        ArrayList<String> querySet = getSorts();
        Double total = 0.0;
        Double paid = 0.0;
        for (String s : querySet) {
            Double price = getPrices().get(s);
            String search = s.replaceAll(" ", "");
            System.out.println(search);
            rs = st.executeQuery("select * from " + sTable + " where Username='" + user + "'");
            while (rs.next()) {
                Double sum = Math.round((price * Integer.parseInt(rs.getString(search))) * 100.) / 100.;
                out += "<td>" + s + "</td><td>" + rs.getString(search) + "</td><td>" + sum + "&nbsp;&euro;</td></tr>";
                paid = rs.getDouble("Paid");
                total += sum;
            }
        }
        total = Math.round(total * 100.0) / 100.0;
        paid = Math.round(paid * 100.0) / 100.0;
        Double invoice = total - paid;
        out += "</tbody></table><table class=\"Prices\"><thead>" +
                "<th>Total</th></thead>" +
                "<tbody>" +
                "<tr><td>Summe:&nbsp;</td><td>" + total + "&nbsp;&euro;</td></tr>" +
                "<tr><td>Gezahlt:&nbsp;</td><td>" + paid + "&nbsp;&euro;</td></tr>" +
                "<tr><td>Rechnungsbetrag:&nbsp;</td><td>" + invoice + "&nbsp;&euro;</td></tr>" +
                "</tbody></table>";
        return out;
    }

    public String getCustomers(String index, String status) throws UnsupportedEncodingException, SQLException {
        customers.clear();
        String out = "<table><tbody><tr>";
        try {
            Statement st = null;
            ResultSet rs = null;
            st = connection.createStatement();
            rs = st.executeQuery("select * from " + sTable);

            int i = 0;

            while (rs.next()) {
                String foundIndex = rs.getString("Nachname").substring(0, 1);
                System.out.println(foundIndex);
                String foundStatus = rs.getString("Status");
                System.out.println(foundStatus);
                if (foundIndex.equals(index) && foundStatus.equals(status)) {
                    String value = rs.getString("Nachname") + ", " + rs.getString("Vorname");
                    customers.add(value);
                    forename.put(value, rs.getString("Vorname"));
                    surname.put(value, rs.getString("Nachname"));
                }

            }
            Collections.sort(customers);

            for (String s : customers) {

                String link = "drinks.jsp?fn=" + forename.get(s) + "&sn=" + surname.get(s);


                if (i > 3) {
                    out += "<tr>";
                }
                out += "<td>" +
                        "<a href=\"" + link + "\">" +
                        s +
                        "</a>" +
                        "</td>";

                if (i > 3) {

                    i = 0;
                }
                i++;
            }
        } catch (SQLException ex) {
            logger.log(logger.LOG_ERR, ex.toString());
        }
        return out += "</tr></tbody></table>";
    }
}